package cn.com.shopec.erp.provideinterface.commo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Component;

import cn.com.shopec.erp.common.constant.SZSCMConstant;
import cn.com.shopec.erp.common.utils.JDBCUtils;
import cn.com.shopec.erp.common.utils.LogicUtil;
import cn.com.shopec.erp.product.dao.IProductDao;
import cn.com.shopec.erp.product.entity.Product;

@Component
public class UpdateProdNoSpace {
	private final static Logger log = Logger.getLogger(UpdateProdNoSpace.class);

	@Resource
	private IProductDao productDao;

	@Resource(name = "jdbcMap")
	private Map<String, String> jdbcMap;

	/**
	 * 
	 * @Title: updateProdNoSpec
	 * @Description: 去掉货品编号空格
	 * @throws
	 * @author: duhui
	 * @date: 2013-7-18下午05:55:31
	 */
	public boolean updateProdNoSpec() {

		boolean isSuccess = false;
		try {
			JDBCUtils.getInstance().initProperty(this.jdbcMap);
			DetachedCriteria detachedCriteria = this.productDao.createCriteria();
			detachedCriteria.add(Restrictions.eq("deleteFlag",SZSCMConstant.DELETE_FLAG_NO));
			detachedCriteria.add(Restrictions.like("prodNo", "% %"));
			List<Product> productList = this.productDao.findByCriteria(detachedCriteria);

			if (LogicUtil.isNotNullAndEmpty(productList)) {
				for (Product product : productList) {
					if (LogicUtil.isNotNullAndEmpty(product.getProdNo())) {
						// 修改之前的货品编号
						String oldProdNo = product.getProdNo();

						String newProdNo = oldProdNo.replace(" ", "");

						String[] tables = { "tbl_product",				//货品表
											"tbl_product",				//货品表
											"tbl_requisition_product",	//请购货品表
											"TBL_PO_PRODUCT" ,			//采购货品表
											"TBL_PROD_SPEC_VALUE",		//货品规格表
											"TBL_PROD_SUPPLIER",		//货品供应商表
											"TBL_PRODUCT_HISTORY",		//货品历史表
											"tbl_re_po_product",		//退货货品表
											"tbl_profits_product",      //商品让利
											"tbl_profits_product",      //商品让利
											"tbl_profits_product_his",  //商品让利历史
											"tbl_profits_product_his",   //商品让利历史
											"TBL_PO_SETTLEMENT_DETAIL",
											"TBL_CONSIGNMENT_S_DETAILL",
											"tbl_invent_record",
											"TBL_WH_ENTRY_PRODUCT",
											"TBL_WH_RETURN_PRODUCT",
											"TBL_ORDER_PRODUCT",
											"TBL_ORDER_RESHIP_DETAIL",
											"tbl_month_in_out_collect"
											};
						//货品条码
						String[] prodNoColumn = {"PROD_NO","prod_bar_code","PP_PRODUCT_NO","prod_no",
												 "PROD_NO","PROD_NO","G_NO","p_no","product_code",
												 "prod_bar_code","product_code","prod_bar_code",
												 "product_no","PRODUCT_NO","PRODUCT_NO","PROD_NO",
												 "PROD_NO","PRODUCT_CODE","PRODUCT_CODE","sku"};
						for (int i = 0; i < tables.length; i++) {
							String sql = "update ";
							sql += tables[i];
							sql += " set " + prodNoColumn [i] + " = '" + newProdNo+"'";
							sql += " where " + prodNoColumn [i] + " = '" + oldProdNo+"'";
							isSuccess = this.updateProdNo(sql);
						}
					}
				}
			}
			isSuccess = true;
		} catch (Exception e) {
			log.error("updateProdNoSpec" + e.getMessage());
			isSuccess = false;
		}

		return isSuccess;

	}

	private boolean updateProdNo(String sql) {
		boolean isSuccess = false;
		try {
			isSuccess = JDBCUtils.getInstance().saveOrUpdate(sql, null);
		} catch (Exception e) {
			log.error("updateProdNo" + e.getMessage());
			e.printStackTrace();
		}
		return isSuccess;
	}
	
	public boolean updateError(String column,String table){
		JDBCUtils jdbc = null;
		Connection conn = null; // 获取数据库连接
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
		try {
			JDBCUtils.getInstance().initProperty(this.jdbcMap);
			String sql = "select "+column+" from " +table +" where "+column+" like '% %'";
			jdbc = JDBCUtils.getInstance();
			conn =  jdbc.getConnection();
			pstmt = conn.prepareCall(sql);
			resultSet = pstmt.executeQuery();
			while(resultSet.next()){
				String value = resultSet.getString(column);
				String newVal = value.replace(" ", "");
				System.out.println(value);
				String sqlU = "update " +table +" set "+column+" = '"+newVal+ "' where "+column+"= '"+ value+"'";
				JDBCUtils.getInstance().saveOrUpdate(sqlU, null);
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}finally{
			jdbc.close(pstmt, conn);
		}
		
	}
}
